package com.prs.crm.report;

import java.io.IOException;
import java.util.List;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import java.io.FileOutputStream;
import com.prs.crm.domain.crm.Dispatch;
import com.prs.crm.domain.crm.DispatchBook;

public class ExcelReport {
	
	
	public  String ReturnNum(List<Dispatch> ld,List<DispatchBook> ldb) throws IOException{
	
		String retrunV="0";
		String fileName="海风运输公司";
		/**
		 * 建立新HSSFWorkbook对象
		 */
		HSSFWorkbook wb = new HSSFWorkbook();   
		/**
		 * 字体设置
		 */
		HSSFFont font = wb.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)20);//设置字体大小
		font.setBoldweight(font.BOLDWEIGHT_NORMAL);
		/**
		 * 建立新的sheet对象,名称
		 */
	
		if(ld.size()!=0){
			/**
			 * ------------------------------------------------------------------------------------------------发货单
			 */
			HSSFSheet sheet = wb.createSheet("海风运输有限公司发货单");
			sheet.addMergedRegion(new Region(0,(short)0,1,(short)9));
			/**
			 * cell样式
			 */
			HSSFCellStyle cellStyle = wb.createCellStyle(); 
			HSSFCellStyle cellStyleSize = wb.createCellStyle(); 
			HSSFCellStyle cellStyleleft = wb.createCellStyle(); 
			/**
			 * 创建标题
			 */		
			HSSFRow row = sheet.createRow((short)0);
			HSSFCell cell = row.createCell((short)0);  
			cell.setCellValue("海风运输有限责任公司");  
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyleSize.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyleleft.setAlignment(HSSFCellStyle.ALIGN_LEFT) ;
			cell.setCellStyle(cellStyle); //设置标题居中
			cell.setCellStyle(cellStyleSize); //设置标题大小		
			cellStyleSize.setFont(font);		
	    	/**
	    	 * 地址栏
	    	 */
			HSSFRow rowdz = sheet.createRow((short)2);	
			sheet.addMergedRegion(new Region(2,(short)0,2,(short)9));
			HSSFCell cellhhz = rowdz.createCell((short)0);  
			cellhhz.setCellValue("成都--->上海"); 
			cellhhz.setCellStyle(cellStyleleft); 
			/**
		     *  导航栏
		     */		
			HSSFRow row1 = sheet.createRow((short)3);	
			sheet.addMergedRegion(new Region(3,(short)0,4,(short)0));
			HSSFCell cellhh = row1.createCell((short)0);  
			cellhh.setCellValue("货号"); 
			cellhh.setCellStyle(cellStyle); 
			sheet.addMergedRegion(new Region(3,(short)1,4,(short)1));
			HSSFCell cellsk = row1.createCell((short)1);  
			cellsk.setCellValue("收款人姓名");
			cellsk.setCellStyle(cellStyle);		
			sheet.addMergedRegion(new Region(3,(short)2,4,(short)2));
			HSSFCell celllx = row1.createCell((short)2);  
			celllx.setCellValue("联系电话");
			celllx.setCellStyle(cellStyle);	
			sheet.addMergedRegion(new Region(3,(short)3,4,(short)3));
			HSSFCell cellpm = row1.createCell((short)3);  
			cellpm.setCellValue("品名");
			cellpm.setCellStyle(cellStyle);		
			sheet.addMergedRegion(new Region(3,(short)4,4,(short)4));
			HSSFCell cellsl = row1.createCell((short)4);  
			cellsl.setCellValue("数量");
			cellsl.setCellStyle(cellStyle);		
			sheet.addMergedRegion(new Region(3,(short)5,4,(short)5));
			HSSFCell cell1 = row1.createCell((short)5);  
			cell1.setCellValue("代收款");
			cell1.setCellStyle(cellStyle);		
			sheet.addMergedRegion(new Region(3,(short)6,3,(short)8));
			HSSFCell celyf = row1.createCell((short)6);  
			celyf.setCellValue("运费");
			celyf.setCellStyle(cellStyle);		
			HSSFRow row3 = sheet.createRow((short)4);
			HSSFCell cellth = row3.createCell((short)6);  
			cellth.setCellValue("提货");
			cellth.setCellStyle(cellStyle);
			HSSFCell cellyif = row3.createCell((short)7);  
			cellyif.setCellValue("已付");
			cellyif.setCellStyle(cellStyle);
			HSSFCell cellhfg = row3.createCell((short)8);  
			cellhfg.setCellValue("回付");
			cellhfg.setCellStyle(cellStyle);
			sheet.addMergedRegion(new Region(3,(short)9,4,(short)9));
			HSSFCell celshr = row1.createCell((short)9);  
			celshr.setCellValue("代收款签字");
			celshr.setCellStyle(cellStyle);
			/**
			 * 为每项数值填充值
			 */
	       int allSize=ld.size();	
	       int startNum=5;
			for(int i=0;i<allSize;i++){  
			Dispatch dis_on=ld.get(i);	
			HSSFRow row5 = sheet.createRow((short)startNum);	
			HSSFCell cellr1 = row5.createCell((short)0);  
			cellr1.setCellValue(dis_on.getTransportCode()); 			
			HSSFCell cellr2 = row5.createCell((short)1);  
			cellr2.setCellValue(dis_on.getReceivePerson()); 		
			HSSFCell cellr3 = row5.createCell((short)2);  
			cellr3.setCellValue(dis_on.getTelepone()); 			
			HSSFCell cellr4 = row5.createCell((short)3);  
			cellr4.setCellValue(dis_on.getProductName()); 			
			HSSFCell cellr5 = row5.createCell((short)4);  
			cellr5.setCellValue(dis_on.getProductNum()); 			
			HSSFCell cellr6 = row5.createCell((short)5);  
			cellr6.setCellValue(dis_on.getAgancyFund()); 			
			HSSFCell cellr7 = row5.createCell((short)6);  
			cellr7.setCellValue(dis_on.getSubmitFund()); 			
			HSSFCell cellr8 = row5.createCell((short)7);  
			cellr8.setCellValue(dis_on.getPaidFund()); 			
			HSSFCell cellr9 = row5.createCell((short)8);  
			cellr9.setCellValue(dis_on.getConterpartFund()); 			
			HSSFCell cellr10 = row5.createCell((short)9);  
			cellr10.setCellValue(dis_on.getAnotherPerson()); 	
			startNum++;
			}
			retrunV="1";
		}
		if(ldb.size()!=0){
			/**
			 * ------------------------------------------------------------------------------------------------收款账簿
			 */	
			HSSFSheet sheet_zb = wb.createSheet("海风运输有限公司收款账簿");
			sheet_zb.addMergedRegion(new Region(0,(short)0,1,(short)9));
			/**
			 * cell样式
			 */
			HSSFCellStyle cellStyle_zb = wb.createCellStyle(); 
			HSSFCellStyle cellStyleSize_zb = wb.createCellStyle(); 
			HSSFCellStyle cellStyleleft_zb = wb.createCellStyle(); 
			/**
			 * 创建标题
			 */		
			HSSFRow row_zb = sheet_zb.createRow((short)0);
			HSSFCell cell_zb = row_zb.createCell((short)0);  
			cell_zb.setCellValue("海风运输有限公司代收款账簿");  
			cellStyle_zb.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyleSize_zb.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyleleft_zb.setAlignment(HSSFCellStyle.ALIGN_LEFT) ;
			cell_zb.setCellStyle(cellStyle_zb); //设置标题居中
			cell_zb.setCellStyle(cellStyleSize_zb); //设置标题大小		
			cellStyleSize_zb.setFont(font);		
			/**
			 * 导航栏
			 */
			HSSFRow row1_zb = sheet_zb.createRow((short)2);	
			HSSFCell cellrq = row1_zb.createCell((short)0);  
			cellrq.setCellValue("日期"); 
			cellrq.setCellStyle(cellStyle_zb); 			
			sheet_zb.addMergedRegion(new Region(2,(short)1,2,(short)2));
			HSSFCell cellcd = row1_zb.createCell((short)1);  
			cellcd.setCellValue("编号"); 
			cellcd.setCellStyle(cellStyle_zb); 			
			sheet_zb.addMergedRegion(new Region(2,(short)3,2,(short)4));
			HSSFCell cellxm= row1_zb.createCell((short)3);  
			cellxm.setCellValue("姓名"); 
			cellxm.setCellStyle(cellStyle_zb); 			
			HSSFCell cellje = row1_zb.createCell((short)5);  
			cellje.setCellValue("金额"); 
			cellje.setCellStyle(cellStyle_zb); 
			HSSFCell cellck = row1_zb.createCell((short)6);  
			cellck.setCellValue("酬款日期"); 
			cellck.setCellStyle(cellStyle_zb); 			
			HSSFCell cellqkrq = row1_zb.createCell((short)7);  
			cellqkrq.setCellValue("取款日期"); 
			cellqkrq.setCellStyle(cellStyle_zb); 
			sheet_zb.addMergedRegion(new Region(2,(short)8,2,(short)9));
			HSSFCell cellqkq= row1_zb.createCell((short)8);  
			cellqkq.setCellValue("取款人签字"); 
			cellqkq.setCellStyle(cellStyle_zb); 
		/**
		 * 账簿填充值	
		 */
	   int allSize_zb=ldb.size();
	   int startNum_zb=3;
	   for(int i=0;i<allSize_zb;i++){	
		   DispatchBook  disbk=ldb.get(i);
		   HSSFRow row1_zbs = sheet_zb.createRow((short)startNum_zb);  
		   HSSFCell cellrqs = row1_zbs.createCell((short)0);  
		   cellrqs.setCellValue(disbk.getBoDate()); 
		   cellrqs.setCellStyle(cellStyle_zb); 
		   sheet_zb.addMergedRegion(new Region(startNum_zb,(short)1,startNum_zb,(short)2));
			HSSFCell cellcds = row1_zbs.createCell((short)1);  
			cellcds.setCellValue(disbk.getBoCode()); 
			cellcds.setCellStyle(cellStyle_zb); 			
			sheet_zb.addMergedRegion(new Region(startNum_zb,(short)3,startNum_zb,(short)4));
			HSSFCell cellxms= row1_zbs.createCell((short)3);  
			cellxms.setCellValue(disbk.getBoName()); 
			cellxms.setCellStyle(cellStyle_zb); 		
			HSSFCell celljes = row1_zbs.createCell((short)5);  
			celljes.setCellValue(disbk.getBoAmount()); 
			celljes.setCellStyle(cellStyle_zb); 				
			HSSFCell cellcks = row1_zbs.createCell((short)6);  
			cellcks.setCellValue(disbk.getBoCDate()); 
			cellcks.setCellStyle(cellStyle_zb); 	
			HSSFCell cellqkrqs = row1_zbs.createCell((short)7);  
			cellqkrqs.setCellValue(disbk.getBoQDate()); 
			cellqkrqs.setCellStyle(cellStyle_zb); 
			sheet_zb.addMergedRegion(new Region(startNum_zb,(short)8,startNum_zb,(short)9));
			HSSFCell cellqkqs= row1_zbs.createCell((short)8);  
			cellqkqs.setCellValue(disbk.getBoAcName()); 
			cellqkqs.setCellStyle(cellStyle_zb); 	      
		   startNum_zb++;
	   }
	   
			
			retrunV="2";
		}

		if("1".equals(retrunV) || "2".equals(retrunV)){
			Dispatch dis_on=ld.get(0);
	        fileName=dis_on.getDispageNum()+fileName;
		FileOutputStream fileOut = new FileOutputStream("c:/"+fileName+".xls");
		wb.write(fileOut);
	    fileOut.close();
		
		
		}

		
		return null;
		
	}
	

}
